package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Banner;
import cn.waka.sys.admin.object.Column;

@Component
public class BannerDao extends BaseDao {
	
	public int save(final Banner banner) {
		KeyHolder holder = new GeneratedKeyHolder();
		this.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sql = "INSERT INTO s_banner(title,img,resource_id,`status`,is_del,sort,c_time)VALUES(?,?,?,1,0,?,?)";
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, banner.getTitle());
				ps.setString(2, banner.getImg());
				ps.setInt(3, banner.getResource_id());
				ps.setInt(4, maxBannerSort()+1);
				ps.setTimestamp(5, banner.getC_time());
				return ps;
			}
		}, holder);
		int id = holder.getKey().intValue();
		return id;
	}
	
	public int update(Banner banner) {
		int rs = this.update("UPDATE s_banner SET title=?,img=?,resource_id=? WHERE id=?",
				banner.getTitle(),
				banner.getImg(),
				banner.getResource_id(),
				banner.getId());
		return rs;
	}
	
	//banner列表
	public List<Map<String,Object>> getBannerList(){	
		String sql = "SELECT id,title,img,resource_id,status,c_time FROM s_banner WHERE is_del=0 ORDER BY sort DESC";

		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				row.put("title", rs.getString(2));
				row.put("img", rs.getString(3));
				row.put("resource_id", rs.getInt(4));
				row.put("status", rs.getInt(5));
				row.put("c_time", rs.getTimestamp(6));
				return row;
			}
		});

		return list;
	}
	//查找一个banner
	public Banner getBannerById(int id){
		String sql="SELECT b.`id`,b.`title`,b.`img`,b.`resource_id`, b.`status`,b.`c_time`,b.`sort`,r.`title` FROM s_banner b LEFT JOIN s_resource r ON b.`resource_id` = r.`id` WHERE b.`id`=?";
		
		List<Banner> list=this.query(sql, new RowMapper<Banner>(){
			@Override
			public Banner mapRow(ResultSet rs, int rowNum) throws SQLException {
				Banner banner=new Banner();
				banner.setId(rs.getInt(1));
				banner.setTitle(rs.getString(2));
				banner.setImg(rs.getString(3));
				banner.setResource_id(rs.getInt(4));
				banner.setStatus(rs.getInt(5));
				banner.setC_time(rs.getTimestamp(6));
				banner.setSort(rs.getInt(7));
				banner.setResource_name(rs.getString(8));;
				return banner;
			}
			
		},id);
		
		return list.size()>0?list.get(0):null;
	}
		
	
	//显示|隐藏
	public int isShow(int id,int status) {
		String usql = "update s_banner set status=? where id=?";
		int rs = this.update(usql, status,id);
		return rs;
	}

	//置顶banner
	public int top_banner(int id,int maxSort) {
		int newSort = maxSort+1;
 		String sql = "UPDATE s_banner SET sort=? WHERE id=?";
		return this.update(sql,newSort,id);
	}
	
	public int top_cut(int sort){
		String sql = "UPDATE s_banner SET sort=sort-1 WHERE sort>?";
		return this.update(sql , sort);
	}
	
	public int maxBannerSort(){
		String sql="SELECT MAX(sort) FROM (SELECT id,sort FROM s_banner)AS X";
		List<Integer> list = this.query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				int value = rs.getInt(1);
				return value;
			}
		});
		
		return list.get(0);
	}
	
	//上移
	public int up_banner(int id,int sort){
		String sql = "UPDATE s_banner SET sort=? WHERE id=?";
		return this.update(sql , sort+1,id);
	}
	
	public int up_cut(int sort){
		String sql = "UPDATE s_banner SET sort=? WHERE sort=?";
		return this.update(sql , sort , sort+1);
	}
	
	//下移
	public int down_banner(int id,int sort){
		String sql = "UPDATE s_banner SET sort=? WHERE id=?";
		return this.update(sql ,sort-1, id);
	}
	public int down_cut(int sort){
		String sql = "UPDATE s_banner SET sort=? WHERE sort=?";
		return this.update(sql , sort , sort-1);
	}

	//广告推荐列表
	public List<Map<String,Object>> getColumnList(){	
		String sql = "SELECT id,type,status,sort FROM s_column ORDER BY sort DESC";

		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				int type = rs.getInt(2);
				row.put("id", rs.getInt(1));
				row.put("type", type);
				row.put("status", rs.getInt(3));
				row.put("sort", rs.getInt(4));
				if(type==1){
					int num = countBanner();
					row.put("num", num);
				}else if(type==2){
					int num = countKinds();
					row.put("num", num);
				}else {
					int num = countSelected();
					row.put("num", num);
				}
				return row;
			}
		});

		return list;
	}
	
	public int countBanner(){
		String sql= "SELECT id as num FROM s_banner WHERE `status`=1";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				return row;
			}
		});
		return list.size();
	}
	
	//count影片分类
	public int countKinds(){
		String sql= "SELECT id FROM w_dic WHERE `key` LIKE '1,173,%' AND `status` = 1";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				return row;
			}
		});
		return list.size();
	}
	
	//count精选影片
	public int countSelected(){
		String sql= "SELECT id FROM s_resource WHERE is_selected = 1 ";
		List<Map<String,Object>> list = this.query(sql, new RowMapper<Map<String,Object>>() {
			@Override
			public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map<String,Object> row=new HashMap<String,Object>();
				row.put("id", rs.getInt(1));
				return row;
			}
		});
		return list.size();
	}
	
	//查找一个column
	public Column getColumnById(int id){
		String sql="SELECT id,`type`,`status`,sort FROM s_column WHERE id =?";
		
		List<Column> list=this.query(sql, new RowMapper<Column>(){
			@Override
			public Column mapRow(ResultSet rs, int rowNum) throws SQLException {
				Column column=new Column();
				column.setId(rs.getInt(1));
				column.setType(rs.getInt(2));
				column.setStatus(rs.getInt(3));
				column.setSort(rs.getInt(4));
				return column;
			}
			
		},id);
		
		return list.size()>0?list.get(0):null;
	}
	
	//显示|隐藏
	public int showOrHidden(int id,int status) {
		String sql = "update s_column set status=? where id=?";
		int rs = this.update(sql, status,id);
		return rs;
	}

	//置顶column
	public int top_column(int id,int maxSort) {
		int newSort = maxSort+1;
 		String sql = "UPDATE s_column SET sort=? WHERE id=?";
		return this.update(sql,newSort,id);
	}
	
	public int top_del(int sort){
		String sql = "UPDATE s_column SET sort=sort-1 WHERE sort>?";
		return this.update(sql , sort);
	}
	
	public int maxSort(){
		String sql="SELECT MAX(sort) FROM (SELECT id,sort FROM s_column)AS X";
		List<Integer> list = this.query(sql, new RowMapper<Integer>() {
			@Override
			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				int value = rs.getInt(1);
				return value;
			}
		});
		
		return list.get(0);
	}
	
	//上移
	public int up_column(int id,int sort){
		String sql = "UPDATE s_column SET sort=? WHERE id=?";
		return this.update(sql , sort+1,id);
	}
	
	public int up_del(int sort){
		String sql = "UPDATE s_column SET sort=? WHERE sort=?";
		return this.update(sql , sort , sort+1);
	}
	
	//下移
	public int down_column(int id,int sort){
		String sql = "UPDATE s_column SET sort=? WHERE id=?";
		return this.update(sql ,sort-1, id);
	}
	public int down_del(int sort){
		String sql = "UPDATE s_column SET sort=? WHERE sort=?";
		return this.update(sql , sort , sort-1);
	}
}
